/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models;


import helpers.Utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

/**
 *
 * @author luci
 */
public class Relationship implements DataModel {
    private int from_user_id;
    private int to_user_id;
    
    private static Logger logger = LogManager.getLogger(Relationship.class.getName());
    
    public final static String TABLE_NAME = "relationships";
    public final static String FROM_USER_ID_FIELD_NAME = "from_user_id";
    public final static String TO_USER_ID_FIELD_NAME = "to_user_id";
    public final static String STATUS_FIELD_NAME = "status";

    public Relationship(int from_id, int to_id) {
        this.from_user_id = from_id;
        this.to_user_id = to_id;
    }
    
   
    @Override
    public int insert() {       
        Connection conn = null;
        int res = -1;
        try{
            conn = Utils.getConnection();            
            StringBuilder query = new StringBuilder("INSERT INTO ");
            query.append(TABLE_NAME);
            query.append(" (");
            query.append(FROM_USER_ID_FIELD_NAME);
            query.append(", ");//1
            query.append(TO_USER_ID_FIELD_NAME);  //2          
            query.append(") VALUES (?,?)");//2 params

            PreparedStatement ps =
                    conn.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

            ps.setInt(1, from_user_id);
            ps.setInt(2, to_user_id);            
            res = ps.executeUpdate();
            logger.info("User "+from_user_id+" added User "+ to_user_id+" as a friend");
        }catch(SQLException ex){
            logger.error("Exception when creating new Relationship "
                    +from_user_id+" - "+ to_user_id, ex);            
        }finally{
            try {
                if (conn != null && !conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException ex) {
                logger.error("Error closing connection in Relationship class", ex);
            }            
        }            
        return res;
    }

    @Override
    public DataModel selectById(long id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int update() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int delete() {
        int res = -1;
        Connection conn = null;
        
        try{
            conn = Utils.getConnection();
            StringBuilder query = new StringBuilder();
            query.append("DELETE FROM ");
            query.append(TABLE_NAME);
            query.append(" WHERE ");
            query.append(FROM_USER_ID_FIELD_NAME);
            query.append(" =? AND ");//1
            query.append(TO_USER_ID_FIELD_NAME);  //2          
            query.append(" =? ");
            
            PreparedStatement ps =
                    conn.prepareStatement(query.toString());
            ps.setInt(1, from_user_id);
            ps.setInt(2, to_user_id);
            res = ps.executeUpdate();
            
        }catch(SQLException ex){
             logger.error("Exception when deleting new Relationship "
                    +from_user_id+" - "+ to_user_id, ex);   
        }finally{
            try {
                if(conn!=null && !conn.isClosed())
                    conn.close();
            } catch (SQLException ex) {
                logger.error("Error closing connection in Relationship class", ex);
            }           
        }
        return res;
    }

    @Override
    public List<DataModel> selectByAttribute(String name, String value) {
        throw new UnsupportedOperationException("Not supported yet");
    }
    
    public static List<User> getRelationships(int userId, int initalIndex, int numberOf){
        Connection conn = null;
        List<User> users = null;
        try{
            conn = Utils.getConnection();            
            StringBuilder query = new StringBuilder();
            query.append("SELECT u.");
            query.append(User.NAME_FIELD_NAME);
            query.append(", u.");
            query.append(User.LNAME_FIELD_NAME);
            query.append(", u.");
            query.append(User.ID_FIELD_NAME);            
            query.append(" FROM ");
            query.append(User.TABLE_NAME);
            query.append(" u ,");
            query.append(Relationship.TABLE_NAME);
            query.append(" r ");
            query.append(" WHERE r.");
            query.append(FROM_USER_ID_FIELD_NAME);
            query.append(" =? AND r.");//1
            query.append(TO_USER_ID_FIELD_NAME);
            query.append(" = u.");
            query.append(User.ID_FIELD_NAME);
            query.append(" LIMIT ?, ?");//2, 3
            
            
            PreparedStatement ps =
                    conn.prepareStatement(query.toString());
            ps.setInt(1, userId);            
            ps.setInt(2, initalIndex);
            ps.setInt(3, numberOf);
            ResultSet rs = ps.executeQuery();
            users = new ArrayList<User>();
            while (rs.next()) {       
               User user = new User();
               user.setName(rs.getString(User.NAME_FIELD_NAME));
               user.setLastName(rs.getString(User.LNAME_FIELD_NAME));
               user.setId(rs.getInt(User.ID_FIELD_NAME));
               users.add(user);
            }
            rs.close();
        }catch(SQLException ex){
            logger.error("Exception when getting relationships", ex);
        }finally{
            try {
                if(conn!=null && !conn.isClosed())
                    conn.close();
            } catch (SQLException ex) {
                logger.error("Error closing connection in Relationship class", ex);
            }           
        }    
        return users;        
    }    
}
